﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.OleDb;

/// <summary>
///DBHander 数据库类
/// </summary>
namespace MySpace.Data
{
    public class DBHandler
    {
        /// <summary>
        /// 实例化构造函数
        /// </summary>
        /// <param name="strconn"></param>
        public DBHandler(string strconn)
        {
            this.dbConnection = new OleDbConnection(strconn);
        }

        //连接对象，在子类的构造方法中生成
        protected OleDbConnection dbConnection = null;

        //事务对象
        protected OleDbTransaction dbTransaction = null;

        //是否在实务中
        protected bool IsInTransaction = false;

        private string commandText = string.Empty;/*属性SQL语句*/
        /// <summary>
        /// SQL语句或储存过程（属性）
        /// </summary>
        public string CommandText
        {
            get { return this.commandText; }
            set { this.commandText = value; }
        }

        private CommandType commandType = CommandType.Text;
        /// <summary>
        /// 当前执行类型是执行SQL语句还是执行储存过程（属性）
        /// </summary>
        public CommandType CommandType
        {
            get { return this.commandType; }
            set { this.commandType = value; }
        }



        //记录SQL参数“名值对”的系列，在子类中构建DbCommand对象时需要使用
        protected List<Parameter> parameters = new List<Parameter>();

        //用于分页查询，检查当前SQL语句是否符合查询基本要求
        protected void CheckPageSQL()
        {
            this.CommandType = System.Data.CommandType.Text;
            if (this.CommandType != System.Data.CommandType.Text)
                throw new System.Exception("分页查询时必须是执行sql语句，而不是执行储存过程");
            if (!this.commandText.Trim().StartsWith("select", true, null))
                throw new System.Exception("SQL语句必须以select开头");
            if (this.IsInTransaction)
                throw new System.Exception("分页查询不能再事务中");
        }

        //构建DbCommand对象
        protected OleDbCommand CreateCommand()
        {
            OleDbCommand dbCommand = new OleDbCommand();
            dbCommand.Connection =this.dbConnection;
            if (IsInTransaction)
            {
                dbCommand.Transaction =this.dbTransaction;
            }
            if (this.CommandType == System.Data.CommandType.TableDirect)
            {
                dbCommand.CommandType = System.Data.CommandType.Text;
                dbCommand.CommandText = string.Format("select * from {0}", this.CommandText);
            }
            else
            {
                dbCommand.CommandType = this.CommandType;
                dbCommand.CommandText = this.CommandText;
            }
            foreach (Parameter p in this.parameters)
            {
                //dbCommand.Parameters.AddWithValue(p.Name, p.Value);
                OleDbParameter pa = new OleDbParameter(p.Name, Convert(p.Value));
                //OleDbParameter pa = new OleDbParameter(p.Name,SqlDbType.Char);
                
                pa.Size =getsize(p.Value);
                pa.Value = p.Value;
                dbCommand.Parameters.Add(pa);

            }
            return dbCommand;
        }

        //构建DataAdapter对象
        protected OleDbDataAdapter CreateAdapter()
        {
            OleDbCommand selectCommand = this.CreateCommand();
            OleDbDataAdapter adapter = new OleDbDataAdapter(selectCommand);
            return adapter;
        }

        //用于Update方法，构建DataAdapter中的UpdateCommand、InsertCommand、DeleteCommand
        protected void BuilderCommand(OleDbDataAdapter adapter)
        {
            new OleDbCommandBuilder(adapter);
        }

        //用于分页查询，获取记录总数


        /// <summary>
        /// 添加参数，将参数添加到list<T>列表类保存起来
        /// </summary>
        /// <param name="parameterName">参数名</param>
        /// <param name="parameterValue">参数值</param>
        public void AddParamerer(string parameterName, object parameterValue)
        {
            this.parameters.Add(new Parameter(parameterName, parameterValue));
        }

        /// <summary>
        ///清空当前的参数列表
        /// </summary>
        public void ClearParameter()
        {
            this.parameters.Clear();
        }


        #region 获取数据的返回值
        /// <summary>
        /// 获取执行结果数据集的第一行第一列的值
        /// </summary>
        /// <returns></returns>
        public object ExecuteScalar()
        {
            try
            {
                if (this.dbConnection.State != ConnectionState.Open)
                    this.dbConnection.Open();
                OleDbCommand command = this.CreateCommand();
                object r = command.ExecuteScalar();
                if (!this.IsInTransaction)
                    this.dbConnection.Close();
                return r;
            }
            catch (Exception ex)
            {
                this.dbConnection.Close();
                throw new Exception(ex.Message + "---出错SQL命令为：：" + this.CommandText);
            }
        }

        /// <summary>
        /// 执行没有数据集的SQL命令，如update，delete，insert等
        /// </summary>
        /// <returns></returns>
        public int ExecuteNonQuery()
        {
            try
            {
                if (this.dbConnection.State != ConnectionState.Open)
                    this.dbConnection.Open();
                OleDbCommand command = this.CreateCommand();
                int r = command.ExecuteNonQuery();
                if (!this.IsInTransaction)
                    this.dbConnection.Close();
                return r;
            }
            catch (Exception ex)
            {
                this.dbConnection.Close();
                throw new Exception(ex.Message + "----出错SQL命令为：" + this.CommandText);
            }
        }

        /// <summary>
        /// 获取数据记录，返回到DataTable中
        /// </summary>
        /// <returns></returns>
        public DataTable ExecuteDataTable()
        {
            try
            {
                if (this.dbConnection.State != ConnectionState.Open)
                    this.dbConnection.Open();
                OleDbDataAdapter adapter = this.CreateAdapter();
                DataTable dt = new DataTable();
                adapter.FillSchema(dt, SchemaType.Mapped);
                adapter.Fill(dt);
                if (!this.IsInTransaction)
                    this.dbConnection.Close();
                return dt;
            }
            catch (Exception ex)
            {
                this.dbConnection.Close();
                throw new Exception(ex.Message + "----出错SQL命令为：" + this.CommandText);
            }
        }

        /// <summary>
        /// 获取空的数据表
        /// </summary>
        /// <returns>返回空数据表</returns>
        public DataTable GetEmptyTable()
        {
            try
            {
                if (this.dbConnection.State != ConnectionState.Open)
                    this.dbConnection.Open();
                OleDbDataAdapter adapter = this.CreateAdapter();
                this.BuilderCommand(adapter);
                DataTable dt = new DataTable();
                adapter.FillSchema(dt, SchemaType.Mapped);
                if (!this.IsInTransaction)
                    this.dbConnection.Close();
                return dt;
            }
            catch (Exception ex)
            {
                this.dbConnection.Close();
                throw new Exception(ex.Message + "----出错SQL命令为：" + this.CommandText);
            }
        }

        private int GetTotalCount()
        {
            this.CheckPageSQL();
            //保留原始SQL语句
            string sql = this.CommandText;
            //将原来SQL语句去掉order by子句后的部分，用语查询记录条数
            string sqlWithOutOrderField = string.Empty;
            int startIndex = sql.ToLower().IndexOf("order by");
            if (startIndex >= 0)
                sqlWithOutOrderField = sql.Substring(0, startIndex);
            else
                sqlWithOutOrderField = sql;
            this.CommandText = string.Format("select count(*) from ({0}) t1", sqlWithOutOrderField);
            int c = int.Parse(this.ExecuteScalar().ToString());
            this.CommandText = sql;
            return c;
        }
        /// <summary>
        /// 分页查询，获取查询到的记录数
        /// </summary>
        public int TotalCount
        {
            get { return this.GetTotalCount(); }
        }
        /// <summary>
        /// 用于分页查询，获取指定页码的数据集
        /// </summary>
        /// <param name="pageSize">当前页码</param>
        /// <param name="currentPageIndex">指定页的记录集</param>
        /// <returns></returns>
        public  DataTable ExecuteDataTable(int pageSize, int currentPageIndex)
        {
            this.CheckPageSQL();
            //保留原始SQL语句
            string sql = this.CommandText;
            //将order by子句的内容保留下来用语row_number() over (order by...)
            string orderBy = string.Empty;
            //将原始SQL语句去掉开头的select以及整个order by子句
            string sqlWithOutSelectAndOrder = sql.Trim().Substring(6);
            int startIndex = sqlWithOutSelectAndOrder.ToLower().LastIndexOf("order by");
            if (startIndex > 0)
            {
                orderBy = sqlWithOutSelectAndOrder.Substring(startIndex);
                sqlWithOutSelectAndOrder = sqlWithOutSelectAndOrder.Substring(0, startIndex);
            }
            else
                throw new Exception("SQL server的分页查询语句必须有order by节");
            //如果pagesize为0，则返回所有数据
            if (pageSize == 0)
            {
                this.CommandText = sql;
                return this.ExecuteDataTable();
            }
            DataTable dt = new DataTable();
            //如果当前页为1，则返回前pagesize跳记录
            if (currentPageIndex == 1)
            {
                this.CommandText = string.Format("select top {0} {1} {2}", pageSize, sqlWithOutSelectAndOrder, orderBy);
                dt = this.ExecuteDataTable();
            }
            else
            {
                //适用SQL Server 2005及以上版本，但必须带order by子句
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat("select * from");
                sb.AppendFormat("(select Row_number() over ({0}) as RowNum,{1}) t1", orderBy, sqlWithOutSelectAndOrder);
                sb.AppendFormat("where RowNum between {0} and {1}", pageSize * (currentPageIndex - 1), pageSize * currentPageIndex - 1);
                this.CommandText = sb.ToString();
                dt = this.ExecuteDataTable();
            }
            this.CommandText = sql;
            return dt;
        }
        #endregion

        #region 把数据更新到数据库中
        /// <summary>
        /// 把数据更新到数据库中
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public int UpdateDate(DataTable dt)
        {
            try
            {
                if (this.dbConnection.State != ConnectionState.Open)
                    this.dbConnection.Open();
                OleDbDataAdapter adapter = this.CreateAdapter();
                if (this.commandType == System.Data.CommandType.StoredProcedure)
                    this.commandType = System.Data.CommandType.Text;
                this.BuilderCommand(adapter);
                int r = adapter.Update(dt);
                if (!this.IsInTransaction)
                    this.dbConnection.Close();
                return r;
            }
            catch (Exception ex)
            {
                this.dbConnection.Close();
                throw new Exception(ex.Message + "--SELECT-SQL为：" + this.commandText);
            }
        }
        #endregion

        #region 实务处理
        /// <summary>
        /// 开始一个事务
        /// </summary>
        public void BegionTransaction()
        {
            try
            {
                if (this.dbConnection.State != System.Data.ConnectionState.Open)
                    this.dbTransaction = this.dbConnection.BeginTransaction();
                this.IsInTransaction = true;
            }
            catch (System.Exception ex)
            {
                this.dbConnection.Close();
                this.IsInTransaction = false;
                throw ex;
            }
        }
        /// <summary>
        /// 回滚一个事务
        /// </summary>
        public void RollbackTransaction()
        {
            try
            {
                this.dbTransaction.Rollback();
                this.dbConnection.Close();
                this.IsInTransaction = false;
            }
            catch (System.Exception ex)
            {
                this.dbConnection.Close();
                this.IsInTransaction = false;
                throw ex;
            }
        }
        /// <summary>
        /// 提交一个事务
        /// </summary>
        public void CommitTransaction()
        {
            try
            {
                this.dbTransaction.Commit();
                this.dbConnection.Close();
                this.IsInTransaction = false;
            }
            catch (System.Exception ex)
            {
                this.dbConnection.Close();
                this.IsInTransaction = false;
                throw ex;
            }

        }
        #endregion

        #region 判别数据对应数据库的数据类型
        public OleDbType Convert(object value)
        {
            string type = value.GetType().Name.ToLower();
            switch (type)
            {
                case "string":
                    return OleDbType.Char;

                case "int32":
                    return OleDbType.Integer;
                case "datetime":
                    return OleDbType.DBTime;
            }
            return OleDbType.Char;
        }
        public int getsize(object value)
        {
            string type = value.GetType().Name.ToLower();
            switch (type)
            {
                case "string":
                    return 50;

                case "int32":
                    return 4;
                case "datetime":
                    return 50;
            }
            return 100;
        }
        #endregion

    }
    #region 参数名值对类
    /// <summary>
    /// 参数名值对类
    /// </summary>
    public class Parameter
    {
        public string Name = string.Empty;
        public object Value = null;
        public Parameter(string name, object value)
        {
            this.Name = name;
            this.Value = value;
        }

    }
    #endregion
}